﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class PHUMUCBLL
    {
        DataService DB = new DataService();

        //get all values
        public List<PHUMUC> getALL()
        {
            string sql = "select * from phumuc";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<PHUMUC> ListPm = new List<PHUMUC>();
            foreach (DataRow r in table.Rows)
            {
                PHUMUC pm = new PHUMUC();
                pm.maphumuc = (int)r[0];
                pm.tenphumuc = (string)r[1];
                pm.machuyenmuc = (int)r[2];
                pm.matinhtrangpm = (int)r[3];

                ListPm.Add(pm);
            }
            return ListPm;
        }

        //insert value
        public void insert(string tenphumuc, int machuyenmuc, int matinhtrangpm)
        {

            string sql = "insert into phumuc values (@tenphumuc,@machuyenmuc,@matinhtrangpm)";
            SqlParameter paraTen = new SqlParameter("tenphumuc", tenphumuc);
            SqlParameter paraMaCm = new SqlParameter("machuyenmuc", machuyenmuc);
            SqlParameter paraMaTtpm = new SqlParameter("matinhtrangpm", matinhtrangpm);

            DB.ExecuteNonQuery(sql, paraTen, paraMaCm, paraMaTtpm);

        }

        public void update(int maphumuc, string tenphumuc, int machuyenmuc, int matinhtrangpm)
        {
            string sql = "update phumuc set tenphumuc = N'" + tenphumuc + "' ,machuyenmuc = " + machuyenmuc.ToString() + " , matinhtrangpm = " + matinhtrangpm.ToString() + " where maphumuc = " + maphumuc.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int maphumuc)
        {
            string sql = "delete from phumuc where maphumuc = " + maphumuc.ToString();
            DB.ExecuteNonQuery(sql);
        }

        //get count
        public int count()
        {
            string sql = "select count(*) from phumuc";
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public List<PHUMUC> getPhumucWithIDCm(int idChuyenmuc) {
            string sql = "select maphumuc,tenphumuc,pm.machuyenmuc,matinhtrangpm "
                        + "from phumuc pm,chuyenmuc cm "
                        + "where pm.machuyenmuc = cm.machuyenmuc "
                        + "and cm.machuyenmuc = " + idChuyenmuc.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<PHUMUC> ListPm = new List<PHUMUC>();
            foreach (DataRow r in table.Rows)
            {
                PHUMUC pm = new PHUMUC();
                pm.maphumuc = (int)r[0];
                pm.tenphumuc = (string)r[1];
                pm.machuyenmuc = (int)r[2];
                pm.matinhtrangpm = (int)r[3];

                ListPm.Add(pm);
            }
            return ListPm;
        }

        public PHUMUC getPhumucWithID(int idPhumuc) {
            string sql = "select * from phumuc where maphumuc = " + idPhumuc.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            PHUMUC pm = new PHUMUC();
            DataRow r = table.Rows[0];
            pm.maphumuc = (int)r[0];
            pm.tenphumuc = (string)r[1];
            pm.machuyenmuc = (int)r[2];
            pm.matinhtrangpm = (int)r[3];

            return pm;
        }

        public int getCountOfBaivietByIdPhumuc(int idPhumuc){
            string sql = "select count(*) from phumuc pm, baiviet bv "
                + " where pm.maphumuc = bv.maphumuc and pm.maphumuc = " + idPhumuc.ToString();
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public string getTinhtrangWithID(int idPhumuc) {
            string sql = "select tentinhtrangpm from phumuc pm,tinhtrangpm ttpm where pm.matinhtrangpm = ttpm.matinhtrangpm and pm.maphumuc = '" + idPhumuc.ToString() + "'";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            return table.Rows[0][0].ToString();
        }
    }
}
